﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Text;
using System.Linq;
using System.Xml.Linq;
using DBSchemaViewer.BackEnd.Entities;
using DBSchemaViewer.BackEnd.CommonHelpers;



namespace DBSchemaViewer.BackEnd.ModelXML
{     
    /// <summary>
    /// CRUD on the relations XML branch
    /// </summary>
    public class RelationDAL
    {
        private DataStoreXML _dataStoreXML = DataStoreXML.Instance;


        public EntityRelation CreateRelation(EntityRelation relation)
        {
            // 1. Generate a unique identifier for this relation (in our case just a unique integer value)
            relation.InternalID = _dataStoreXML.NextID;

            // 2. Generate the relation fields mappings 
            //   <fieldsmapping>
            //      <fieldmapping fieldfromid="000001" fieldtoid="000010"/>
            //      (...)
            //   </fieldsmapping>            
            var relationFieldsMappingsElements = from p in relation.RelationFields
                                                        select new XElement("fieldmapping"
                                                                            , new XAttribute("fieldfromid", p.FieldOrigin)
                                                                            , new XAttribute("fieldtoid", p.FieldDestination)
                                                                            );

            XElement relationFieldMappingsRoot = new XElement("fieldsmapping", relationFieldsMappingsElements);


            // 3. Create the Relation XElement, relations are simple we create all the children in one go
            XElement relationToInsert = new XElement("relation", new XAttribute("id", relation.InternalID),
                                            new XElement("tablefromid", relation.TableOriginID),                                            
                                            new XElement("tabletoid", relation.TableDestinationID),
                                            relationFieldMappingsRoot
                                      );

            // 4.  Append it under the <relation> node
            XElement relationBranch = _dataStoreXML.RelationsBranch;

            relationBranch.Add(relationToInsert);


            return relation;
        }

        /// <summary>
        /// return all DB relations in the XML
        /// </summary>
        /// <returns></returns>
        public EntityRelation[] GetRelations()
        {
            EntityRelation[] relations = null;
            XElement[] relationNodes = null;

            relationNodes = _dataStoreXML.ReturnAllRelations();


            // Build up the table entities base on the XML content


            relations = (from p in relationNodes
                      select new EntityRelation()
                      {
                          InternalID = Convert.ToInt32(p.Attribute("id").Value)
                          ,
                          TableOriginID = Convert.ToInt32(p.Element("tablefromid").Value)
                          ,
                          TableDestinationID = Convert.ToInt32(p.Element("tabletoid").Value)
                          // FIX THIS !!!
                          , LstRelationFields =    (
                                                      from pFieldRels in p.Element("fieldsmapping").Elements("fieldmapping")
                                                      select new EntityRelationField()
                                                      {
                                                          FieldOrigin = Convert.ToInt32(pFieldRels.Attribute("fieldfromid").Value)
                                                          ,
                                                          FieldDestination = Convert.ToInt32(pFieldRels.Attribute("fieldtoid").Value)                                                                                  
                                                      }
                                                    ).ToList<EntityRelationField>()
                                                
                      }
                      ).ToArray();
            
            /*
            relations = (from p in relationNodes
                      select new EntityRelation()
                      {
                          InternalID = Convert.ToInt32(p.Attribute("id").Value)
                          ,
                          TableOriginID = Convert.ToInt32(p.Element("tablefromid").Value)
                          ,
                          TableDestinationID = Convert.ToInt32(p.Element("tabletoid").Value)
                          , RelationFields = ListConverter<EntityRelationField>.ConvertListToObservableCollection
                                             (
                                                 (
                                                  from pFieldRels in p.Element("fieldsmapping").Elements("fieldmapping")
                                                  select new EntityRelationField()
                                                  {
                                                      FieldOrigin = Convert.ToInt32(pFieldRels.Attribute("fieldfromid").Value)
                                                      ,
                                                      FieldDestination = Convert.ToInt32(pFieldRels.Attribute("fieldtoid").Value)                                                                                  
                                                  }
                                              ).ToList<EntityRelationField>()
                                              )
                                  )                           
                      }
                      ).ToArray();
            
            ObservableCollection<EntityRelationField> test;
           */
            

            return relations;
        }

        /// <summary>
        /// Returns the relations entities that has as origin or destsination the given
        /// table ID
        /// </summary>
        /// <param name="tableID"></param>
        /// <returns></returns>
        public EntityRelations GetRelationsByTableID(int tableID)
        {
            XElement[] relationsNodes = _dataStoreXML.SearchRelationsByTableID(tableID);
            EntityRelations entityRelations = null;

            if (relationsNodes != null)
            {
                // TODO: Fields is a collection, we will have to modify the XSD
                // and make a bit more complex this select (we need to nest two selects
                // in order to build up the fields origins and destinations mappings
                // this will include as well a change in the entity :-O
                List<EntityRelation> tempRelations = (from p in relationsNodes
                                select new EntityRelation()
                                {
                                    InternalID = Convert.ToInt32(p.Attribute("id").Value)
                                    ,TableOriginID = Convert.ToInt32(p.Element("tablefromid").Value)                                    
                                    ,TableDestinationID = Convert.ToInt32(p.Element("tabletoid").Value)
                                    ,
                                    LstRelationFields = (from fmapping in p.Element("fieldsmapping").Elements("fieldmapping")
                                                       select new EntityRelationField
                                                       {
                                                         FieldOrigin = Convert.ToInt32(fmapping.Attribute("fieldfromid").Value)
                                                        ,
                                                         FieldDestination = Convert.ToInt32(fmapping.Attribute("fieldtoid").Value)
                                                       }).ToList<EntityRelationField>()
                                }).ToList<EntityRelation>();

                // It seems that LINQ / C# doesn't like casting from list<mytype> to myclass: list<mytype>
                // ugly stuff :-(
                
                entityRelations = new EntityRelations();
                foreach (EntityRelation currRelation in tempRelations)
                {
                    entityRelations.Add(currRelation);
                }                
            }

            return entityRelations;
            
        }

        /// <summary>
        /// Given an Relation internal ID return the relation entity associated to that internal ID
        /// </summary>
        /// <returns></returns>
        public EntityRelation GetRelationByRelationID(int internalID)
        {
            EntityRelation relation = null;
            XElement relationNode = null;

            relationNode = _dataStoreXML.SearchRelation(internalID);

            if (relationNode != null)
            {
                relation = new EntityRelation()
                {
                    InternalID = internalID,
                    TableOriginID = Convert.ToInt32(relationNode.Element("tablefromid").Value),
                    TableDestinationID = Convert.ToInt32(relationNode.Element("tabletoid").Value),
                    LstRelationFields = (from fmapping in relationNode.Element("fieldsmapping").Elements("fieldmapping")
                                      select new EntityRelationField
                                      {
                                          FieldOrigin = Convert.ToInt32(fmapping.Attribute("fieldfromid").Value)
                                       ,
                                          FieldDestination = Convert.ToInt32(fmapping.Attribute("fieldtoid").Value)
                                      }).ToList<EntityRelationField>()
                }
                    ;

                
            }

            return relation;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fkInternalID"></param>
        /// <returns></returns>
        public int CountNumberOfFKRelationDestinationsASingleFieldHas(int fkInternalID)
        {
            int count  = 0;

            // A bit complex to arm a LINQ query (two nested levels)
            // let's go for an easier way (maybe close performance...)

            // 1. Select all relations
            XElement[] relationNodes = null;

            relationNodes = _dataStoreXML.ReturnAllRelations();

            
            // 2. Loop and execute a query in each relation to check wether
            // the relation contains an FK to the field that we want to check
            foreach (XElement relationNode in relationNodes)
            {
                count += (from pfieldMapping in relationNode.Element("fieldsmapping").Elements("fieldmapping")
                          where Convert.ToInt32(pfieldMapping.Attribute("fieldtoid").Value) == fkInternalID
                          select 1
                         ).Count();
            }


            return count;            
        }


        /// <summary>
        /// Remove a relation from the model
        /// </summary>
        /// <param name="relationID"></param>
        public void DeleteRelation(int relationID)
        {
            _dataStoreXML.DeleteRelation(relationID);
        }


        /// <summary>
        /// Returns relations for a given table, having at least of the fields indicated in the Fields
        /// List as origin or desnation
        /// 
        /// Sample usage of this method: We remove some fields on a table, check if there are relations
        /// affected (that field belongs to an origin or destination relation), in that case add it to the list
        /// (right now in the application we just remove the relation, another approach could be to check if it's
        /// just the only one field), watch out !! later on we must update all FK's in the entity tables :-(, !!! Hey
        /// Remove relation does that for us automatically Pheeeew !!.
        /// 
        /// 1. Go relation by Relation
        /// 2. Check if the relation contains any field that is affected (Origin and Destination)
        /// 3. If the are fields affected add the relation to the list
        /// 
        /// </summary>
        /// <param name="p"></param>
        /// <param name="deletedFields"></param>
        /// <returns></returns>
        public List<int> GetRelationsHaving(int tableID, List<int> fieldsToPing)
        {
            List<int> relationsAffectedIds = new List<int>();

            EntityRelations tableLevelEntityRelations = GetRelationsByTableID(tableID);

            //tableLevelEntityRelations[0].LstRelationFields[0].FieldOrigin
            //tableLevelEntityRelations[0].LstRelationFields[0].FieldDestination

            // 1. Go relation by Relation
            foreach(EntityRelation currRelation in tableLevelEntityRelations)
            {
                int numberOfFieldsAffecting = 0;
                

                // Check if the relation contains any field that is affected
                // 2. Origin and Destination
                numberOfFieldsAffecting = (from prelFields in currRelation.RelationFields
                       where (fieldsToPing.Contains(prelFields.FieldOrigin) || fieldsToPing.Contains(prelFields.FieldDestination))
                       select 1).Count();
                      
                
                // 3. If the are fields affected add the relation to the list
                if(numberOfFieldsAffecting > 0)
                   relationsAffectedIds.Add(currRelation.InternalID);
            }

            return relationsAffectedIds;
        }
    }
}
